package ebcs.scan.controller.projectCostCtr;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import ebcs.database.bean.BsGroupInfor;
import ebcs.database.bean.BsNeedInfor;
import ebcs.database.bean.BsProTeamInfor;
import ebcs.database.bean.BsUserDetailInfo;
import ebcs.database.bean.BsUserInfor;
import ebcs.database.bean.ProjectCostInfo;
import ebcs.database.bean.RpCaptalReportBean;
import ebcs.database.bean.RpPreDictionBean;
import ebcs.database.bean.RpProjectMoneyBean;
import ebcs.database.bean.RpUserReportBean;
import ebcs.database.bean.SysUserFunc;
import ebcs.database.service.BsGroupInforServiceImp;
import ebcs.database.service.BsProTeamServiceImp;
import ebcs.database.service.BsUserNeedServiceImp;
import ebcs.database.service.ProjectCostServiceImp;
import ebcs.database.service.ReportServiceImp;
import ebcs.database.service.SystemServiceImp;
import ebcs.database.service.impl.BsProTeamServiceImpl;
import ebcs.database.service.impl.BsUserNeedServiceImpl;
import ebcs.database.service.impl.ReportServiceImpl;
import ebcs.scan.bean.common.ExcleEntity;
import ebcs.scan.common.consts.UserStatus;
import ebcs.scan.common.utils.ExportDataUtil;
import ebcs.scan.common.utils.StringUtil;
import ebcs.scan.service.wechat.WeChatTemplPushService;
import ebcs.scan.service.wechat.WechatSignTools;
import jplat.core.trans.JAppBaseService;
import jplat.core.trans.JAppContext;
import jplat.core.trans.JAppContextFactory;
import jplat.error.exception.JSystemException;
import jplat.error.exception.JTransException;
import jplat.tools.config.JAppConfig;
import jplat.tools.config.JBusConfig;
import jplat.tools.string.DateUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/***
 * 项目成本查询,下载,导入
 * 
 * @author yf
 * @comment
 */
@Controller
public class ProjectCostCtrl extends JAppBaseService {
	private Logger logger = Logger.getLogger(ProjectCostCtrl.class);

	protected JAppContextFactory serviceFactory = new JAppContextFactory();

	// 数据库库服务.
	@Autowired
	public BsProTeamServiceImp teamService;
	@Autowired
	public BsGroupInforServiceImp groupService;
	@Autowired
	public BsUserNeedServiceImp userNeedService;
	@Autowired
	public ReportServiceImp reportService;
	@Autowired
	public SystemServiceImp systemService;
	@Autowired
	private ProjectCostServiceImp projectCostService;
	
	/**
	 * 上传项目核算成本Excel模板 yf 
	 */
	@RequestMapping("/page/main/upProjectostExcel.do")
	@ResponseBody
	public void downProjectostExcel(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "excelFile", required = true) MultipartFile  excelFile)
			throws JTransException, JSystemException {
		JAppContext context = serviceFactory.buildAppContextNoUser(request, response);// 自动客户信息
		List<ProjectCostInfo> list = new ArrayList<>(); 
		try {
			Workbook wb = Workbook.getWorkbook(excelFile .getInputStream());
			// 获取选项卡对象 第0个选项卡  
	        Sheet sheet = wb.getSheet(0);  
	        int colNum = sheet.getRow(0).length;//获取Excel列数
	        int rows = sheet.getRows();
	        //从第三行开始遍历
	        Pattern pattern = Pattern.compile("\\d{6}");
	        int flag=0;
	        for (int i = 2; i < rows; i++) {
                for (int j = 0; j < 7; j++) {
                    //第一个是列数，第二个是行数
                	String proGroupName=sheet.getCell(j++, i).getContents();
                	String proTeamName=sheet.getCell(j++, i).getContents();
                    String employeeId=sheet.getCell(j++, i).getContents();
                    String name=sheet.getCell(j++, i).getContents();
                    String month=sheet.getCell(j++, i).getContents();
                    String cost=sheet.getCell(j++, i).getContents();
                    String salary=sheet.getCell(j++, i).getContents();
                    
                    System.out.println("proGroupName:"+proGroupName+"proTeamName:"+proTeamName+"employeeId:"+employeeId+" name:"+name+" month:"+month+" cost:"+cost);
                    if(StringUtil.isEmpty(proGroupName)&&StringUtil.isEmpty(proTeamName)&&StringUtil.isEmpty(employeeId)&&
                       StringUtil.isEmpty(name)&&StringUtil.isEmpty(month)&&StringUtil.isEmpty(cost)&&StringUtil.isEmpty(salary)){
                    	flag=1;
        	        	break;
                    }
                   //表格内数据不能有为空
                  /*  if(proGroupName==""||proTeamName==""||employeeId==""||name==""){
                    	context.buildAppResponse("FALSE1");
        	        	return;
                    }*/
                    //验证月份格式
                  if(StringUtil.isNotEmpty(month)){  
                    Matcher matcher = pattern.matcher(month);
                    if(!matcher.matches()){
                    	context.buildAppResponse("FALSE2");
        	        	return;
                    }
                  }
                  if(StringUtil.isNotEmpty(month)&&StringUtil.isNotEmpty(cost)&&StringUtil.isNotEmpty(salary)){//如果月份和成本不为空则导入到数据库
                    String id = UUID.randomUUID().toString();
                    list.add(new ProjectCostInfo(id,employeeId, name, month, cost, salary, proTeamName,proGroupName));
                   }
                }
                if(flag==1){
                	break;
                }
            }
	        //list为空,说明Excel没数据
	        if(list==null||list.size()<=0){
	        	context.buildAppResponse("FALSE3");
	        	return;
	        }
	        //判断是否有重复的工号
//	        Set<ProjectCostInfo> prSet = new HashSet<>(list);
//	        if(prSet.size()!=list.size()){
//	        	context.buildAppResponse("FALSE4");
//	        	return;
//	        }
	       //查询数据的工号和月份,如果数据库中的数据和Excel中的数据(工号和月份有重复),提示数据重复
			List<ProjectCostInfo> rsList = projectCostService.searchProjectcost();
			if(rsList!=null&&rsList.size()>0){
				for (ProjectCostInfo p1 : rsList) {
					if(StringUtil.isNotEmpty(p1.getMonth())&&StringUtil.isNotEmpty(p1.getEmployeeId())){
					for (ProjectCostInfo p2 : list) {
						if(StringUtil.isNotEmpty(p2.getMonth())&&StringUtil.isNotEmpty(p2.getEmployeeId())){
							if(p1.getEmployeeId().equals(p2.getEmployeeId()) && p1.getMonth().equals(p2.getMonth())){
								context.buildAppResponse("FALSE5");
					        	return;
							}
						}
					}
				  }
				}
			}
	        //将得到的数据批量插入数据库
	        projectCostService.insertProjectCostInfos(list);
	        context.buildAppResponse("OK");
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			context.buildAppResponse("FALSE6");
        	return;
		}  catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}  
      
	}
	
	/**
	 * 查询项目成本信息 yf
	 */
	@RequestMapping("/page/main/searchProjectcostInfor.do")
	public void searchProjectcostInfor(HttpServletRequest request, HttpServletResponse response)
			throws JTransException, JSystemException {
		JAppContext context = serviceFactory.buildAppContextNoUser(request, response);// 自动客户信息
		ProjectCostInfo req = context.obtainReqModel(ProjectCostInfo.class);
		req.setEndRow((Integer.parseInt(req.getStartRow()))+(Integer.parseInt(req.getPageSize()))+"");
		List<ProjectCostInfo> rsList = projectCostService.searchProjectcostInfor(req);
		int count = projectCostService.searchProjectCostPageCount(req);
		Map rsModel = new HashMap();
		rsModel.put("count", count);
  		rsModel.put("rsList", rsList);
 		context.buildAppResponse(rsModel);
	}
	
	/**
	 * 删除项目成本信息 yf
	 */
	 @RequestMapping("/page/main/deleteSingleProjectCost.do")
	    public void deleteSingleGroup(HttpServletRequest request, HttpServletResponse response) throws JTransException, JSystemException {
	        JAppContext context = serviceFactory.buildAppContextNoUser(request, response);//自动客户信息
	        ProjectCostInfo req = context.obtainReqModel(ProjectCostInfo.class);
	        int i=projectCostService.deleteProjectCostByEmpId(req.getId());
	        if(i==0){
	        	context.buildAppResponse("FALSE");
	        }
	        if(i==1){
	        	context.buildAppResponse("OK");
	        }
	    }

	 /**
	  * 项目成本模板【导出】
	  */
		@RequestMapping("/page/main/downProjectCostExcelFile.do")
		public void doReportCountExcelFile(HttpServletRequest request, HttpServletResponse response)throws JTransException, JSystemException {
			JAppContext context = serviceFactory.buildAppContextNoUser(request, response);// 自动客户信息
            //查询人员信息
			ProjectCostInfo req = context.obtainReqModel(ProjectCostInfo.class);
			List<ProjectCostInfo> rsList = projectCostService.selectUserInfo(req);
			String path = request.getServletContext().getRealPath("");// 获取项目动态绝对路径
			ExportDataUtil export = new ExportDataUtil();
			ExcleEntity entity = new ExcleEntity();
			entity.setTitle("项目成本导入模板");
			entity.setFileName(DateUtil.getSystemTimeBySomeFormat(DateUtil.FMT_ALL) + ".xls");
			entity.setPath(path);
			Map<Object, String> headMap = new LinkedHashMap<Object, String>();
			headMap.put("proGroupName","团队名称");
			headMap.put("proTeamName","项目组");
			headMap.put("employeeId","工号");
			headMap.put("name","姓名");
			headMap.put("month","月份");
			headMap.put("cost","成本");
			headMap.put("salary","工资");
			
			entity.setHeadMap(headMap);
			export.exportExcelForProjectCost(entity, rsList);
			context.buildAppResponse(entity);
		}
		
//------------------------------------------------------奖励表导入功能分割线--------------------------------------------------------------------------		
	
		/**
		 * 查询项目奖励表导入时数据信息 yf
		 */
		@RequestMapping("/page/main/searchRewardFormInfor.do")
		public void searchRewardFormInfor(HttpServletRequest request, HttpServletResponse response)
				throws JTransException, JSystemException {
			JAppContext context = serviceFactory.buildAppContextNoUser(request, response);// 自动客户信息
			//ProjectCostInfo req = context.obtainReqModel(ProjectCostInfo.class);
			RpProjectMoneyBean req = context.obtainReqModel(RpProjectMoneyBean.class);
			req.setEndRow((Integer.parseInt(req.getStartRow()))+(Integer.parseInt(req.getPageSize()))+"");
			List<RpProjectMoneyBean> rsList = projectCostService.searchRewardFormInfor(req);
			int count = projectCostService.searchRewardFormInforPageCount(req);
			Map rsModel = new HashMap();
			rsModel.put("count", count);
	  		rsModel.put("rsList", rsList);
	 		context.buildAppResponse(rsModel);
		}
		
		
		/**
		 * Excel表中的合同回款金额和项目费用导入到bs_need_info中 yf 
		 */
		@RequestMapping("/page/main/updNeedInfo.do")
		@ResponseBody
		public void updNeedInfo(HttpServletRequest request, HttpServletResponse response,@RequestParam(value = "excelFile", required = true) MultipartFile  excelFile)
				throws JTransException, JSystemException {
			JAppContext context = serviceFactory.buildAppContextNoUser(request, response);// 自动客户信息
			List<RpProjectMoneyBean> list = new ArrayList<>(); 
			try {
				Workbook wb = Workbook.getWorkbook(excelFile .getInputStream());
				// 获取选项卡对象 第0个选项卡  
		        Sheet sheet = wb.getSheet(0);  
		        int colNum = sheet.getRow(0).length;//获取Excel列数
		        int rows = sheet.getRows();
		        //从第三行开始遍历
		        int flag=0;
		        for (int i = 2; i < rows; i++) {
	                for (int j = 1; j < 8; j++) {
	                    //第一个是列数，第二个是行数
	                	String proGroupName=sheet.getCell(j++, i).getContents();
	                	String proTeamName=sheet.getCell(j++, i).getContents();
	                	String contractId=sheet.getCell(j++, i).getContents();
	                    String contractNo=sheet.getCell(j++, i).getContents();
	                    String needNo=sheet.getCell(j++, i).getContents();
	                    String needMoney=sheet.getCell(j++, i).getContents();
	                    String xthdje=sheet.getCell(j++, i).getContents();
	                    String paybackAmount=sheet.getCell(j++, i).getContents();
	                    String projectFee=sheet.getCell(j++, i).getContents();
	                    if(StringUtil.isEmpty(contractId)&&StringUtil.isEmpty(proGroupName)&&StringUtil.isEmpty(proTeamName)&&StringUtil.isEmpty(needNo)&&StringUtil.isEmpty(contractNo)&&StringUtil.isEmpty(xthdje)&&
	                    		StringUtil.isEmpty(needMoney)&&StringUtil.isEmpty(paybackAmount)&&StringUtil.isEmpty(projectFee)){
	                    	flag=1;
	        	        	break;
	                    }
	                    list.add(new RpProjectMoneyBean(proTeamName,needNo,xthdje,paybackAmount,projectFee));
	                }
	                if(flag==1){
	                	break;
	                }
	            }
		        //list为空,说明Excel没数据
		        if(list==null||list.size()<=0){
		        	context.buildAppResponse("FALSE3");
		        	return;
		        }
		       //判断导入的数据和数据库的数据是否有重复
			/*    List<RpProjectMoneyBean> rsList = projectCostService.searchRewardForm();
				if(rsList!=null&&rsList.size()>0){
					for (RpProjectMoneyBean p1 : rsList) {
						for (RpProjectMoneyBean p2 : list) {
							if(p1.getProTeamName().equals(p2.getProTeamName()) && p1.getNeedNo().equals(p2.getNeedNo()) && p1.getPaybackAmount().equals(p2.getPaybackAmount())
									&& p1.getProjectFee().equals(p2.getProjectFee())){
								context.buildAppResponse("FALSE5");
					        	return;
							}
						}
					}
				}*/
		        //将得到的数据批量插入数据库
		        projectCostService.insertNeedInfos(list);
		        context.buildAppResponse("OK");
			} catch (BiffException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				context.buildAppResponse("FALSE6");
	        	return;
			}  catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}  
	      
		}
		
		
		/**
		 * 删除项目成本信息 yf
		 */
		 @RequestMapping("/page/main/deleteRewardForm.do")
		    public void deleteRewardForm(HttpServletRequest request, HttpServletResponse response) throws JTransException, JSystemException {
		        JAppContext context = serviceFactory.buildAppContextNoUser(request, response);//自动客户信息
		        RpProjectMoneyBean req = context.obtainReqModel(RpProjectMoneyBean.class);
		        int i=projectCostService.deleteRewardForm(req);
		        if(i==0){
		        	context.buildAppResponse("FALSE");
		        }
		        if(i==1){
		        	context.buildAppResponse("OK");
		        }
		    }
		
}
